<?php
defined('BASEPATH') or exit('No direct script access allowed');

class StatisticsOtherCollect extends AdminController
{

	function __construct()
	{
		parent::__construct();
		$this->load->model('Student_feedback_model');
		$this->load->model('Supervisor_feedback_model');
		$this->load->model('Admin_model');
		$this->load->model('Table1_model');
		$this->load->model('Table2_model');
		$this->load->model('Table3_model');
		$this->load->model('Table4_model');
		$this->load->model('Table5_model');
		$this->load->model('Table6_model');
		$this->load->library('ProjectService/export_service');
	}

	function Index()
	{
		$data['course_units'] = (new AdminDeptList)->getRemark()[1]; 
		$this->load->view('admin/statisticsOtherCollectList',$data);
	}

	function getDatas($params = array(), $isExport = false)
	{
		if (isset($_POST['type']) && $_POST['type'] == 1) { //其他材料反馈报表
			$this->statisticsOtherDept($params, $isExport);
		} else { //个人其他材料反馈报表
			if(isset($_POST['feedback_type']) && $_POST['feedback_type'] == 2 && isset($_POST['start_time']) && $_POST['start_time']!= '' && isset($_POST['end_time']) && $_POST['end_time'] != ''){
				$this->feedbackStudentOther($params, $isExport);		
			}else{
				$this->statisticsOther($params, $isExport);
			}
			
		}
	}


	//个人督导个人其他材料
	function statisticsOther($params = array(), $isExport = false)
	{
		$connar = array();
		$params = $_POST;
		if (!$isExport) {
			$connar = $this->Admin_model->pickPages($connar, $params);
		}
		$connar['items'] = "DISTINCT (db_admin.id),usercode,username";
		$connar['join'][0] = array('db_table1', 'db_table1.admin_id=db_admin.id');
		$connar['join'][1] = array('db_table2', 'db_table2.admin_id=db_admin.id');
		$connar['join'][2] = array('db_table3', 'db_table3.admin_id=db_admin.id');
		$connar['join'][3] = array('db_table4', 'db_table4.admin_id=db_admin.id');
		$connar['join'][4] = array('db_table5', 'db_table5.admin_id=db_admin.id');
		$connar['join'][5] = array('db_table6', 'db_table6.admin_id=db_admin.id');
		$connar['where'] = $this->sqlEscape('db_admin.status<>?? and role_id =2 and ( FIND_IN_SET(5,db_admin.sub_role_id) or FIND_IN_SET(6,db_admin.sub_role_id) or FIND_IN_SET(11,db_admin.sub_role_id)) and (db_table1.admin_id IS NOT NULL OR db_table2.admin_id IS NOT NULL OR db_table3.admin_id IS NOT NULL OR db_table4.admin_id IS NOT NULL 	OR db_table5.admin_id IS NOT NULL)', array(CommonStatus::Deleted[XPHEnum::Value]));
		$role = explode(',', getSess()['sub_role_id']);
		array_push($role, getSess()['role_id']);
		if (in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)) {
			$connar['where'] .= ' and 1=1 ';
		} else if (in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)) {
			$connar['where'] .= $this->sqlEscape(' and db_admin.dept_name=?? ', array(getSess()['dept_name']));
		} else if (in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::ExternalSupervisor[XPHEnum::Value], $role) || in_array(WebRole::SupervisorDirector[XPHEnum::Value], $role)) {
			$connar['where'] .= $this->sqlEscape(' and db_admin.id=?? ', array(getSess()['id']));
		}
		if (isset($params['dept_name']) && $params['dept_name'] != '-1') {
			$connar['where'] .= $this->sqlLikeEscape(' and dept_name like "%??%"', array($params['dept_name']));
		} else if (isset($_POST['dept_name']) && $_POST['dept_name'] != '-1') {
			$connar['where'] .= $this->sqlLikeEscape(' and dept_name like "%??%"', array($_POST['dept_name']));
		}
		if (isset($params['username'])) {
			$connar['where'] .= $this->sqlLikeEscape(' and username like "%??%"', array($params['username']));
		} else if (isset($_POST['username'])) {
			$connar['where'] .= $this->sqlLikeEscape(' and username like "%??%"', array($_POST['username']));
		}
		if (isset($params['level']) && $params['level'] != -1) {
			if ($params['level'] == FeedBackSubType::School[XPHEnum::Value]) {
				$level = WebRole::SchoolSupervisor[XPHEnum::Value];
			}
			if ($params['level'] == FeedBackSubType::Unit[XPHEnum::Value]) {
				$level = WebRole::UnitSupervisor[XPHEnum::Value];
			}
			$connar['where'] .= $this->sqlEscape(' and (role_id=?? or find_in_set(??,sub_role_id))', array($level, $level));
		} else if (isset($_POST['level']) && $_POST['level'] != -1) {
			if ($_POST['level'] == FeedBackSubType::School[XPHEnum::Value]) {
				$level = WebRole::SchoolSupervisor[XPHEnum::Value];
			}
			if ($_POST['level'] == FeedBackSubType::Unit[XPHEnum::Value]) {
				$level = WebRole::UnitSupervisor[XPHEnum::Value];
			}
			$connar['where'] .= $this->sqlEscape(' and (role_id=?? or find_in_set(??,sub_role_id))', array($level, $level));
		}

		if (isset($params['start_time']) && isset($params['end_time'])) {
			$params['start_time'] = $params['start_time'] . '00:00:00';
			$_POST['start_time'] = $_POST['start_time'] . '00:00:00';
			$params['end_time'] = $params['end_time'] . '23:59:59';
			$_POST['end_time'] = $_POST['end_time'] . '23:59:59';
			// $where.=' and created>'.strtotime($params['start_time']).' and created<'.strtotime($params['end_time']);
			// $connar['where'].=$this->sqlEscape(" and db_table1.created>=?? and db_table1.created<??  and db_table2.created>=?? and db_table2.created<?? and db_table3.created>=?? and db_table3.created<?? and db_table4.created>=?? and db_table4.created<?? and db_table5.created>=?? and db_table5.created<?? and db_table6.created>=?? and db_table6.created<??",array(strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59')));
		}


		if (!$isExport) {
			$data = $this->Admin_model->get_page_list($connar);
			// echo $this->db->last_query();
			// 重新计算条数
			$count = $this->Admin_model->query("SELECT count(*) count from (SELECT DISTINCT 	db_admin.`usercode`,db_admin.`id`,	db_admin.`username` FROM `db_admin` LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id  LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id WHERE " . $connar['where'] . ")a");
			$data['count'] = $count[0]['count'];
			$data["totalPage"] = ceil($data["count"] / $params['pageSize']);
		} else {
			$data["ret"] = $this->Admin_model->get_list_full($connar);
		}

		$adminlist = array();
		foreach ($data['ret'] as $k => $v) {
			// 反馈次数
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where = ' and feedback.created>' . strtotime($params['start_time']) . ' and feedback.created<' . strtotime($params['end_time']);
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$where = ' and feedback.created>' . strtotime($_POST['start_time']) . ' and feedback.created<' . strtotime($_POST['end_time']);
			} else {
				$where = '';
			}
			$feedbackTime = $this->Student_feedback_model->query("select count(*) feedbackTime from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=" . CommonStatus::UnDeleted[XPHEnum::Value] . " and (feedback.status=" . FeedBackStatus::HaveWrite[XPHEnum::Value] . " or feedback.status=" . FeedBackStatus::End[XPHEnum::Value] . ")  and db_admin.id=" . $v['id'] . $where);

			$data['ret'][$k]['feedbackTime'] = $feedbackTime[0]['feedbackTime'];
			

			// 教学督导专题教研次数
			$connar1['where'] = $this->sqlEscape('admin_id=??', array($v['id']));
			$connar1['items'] = 'count(*) table1Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$table1Time = $this->Table1_model->get_list_full($connar1);
			$data['ret'][$k]['table1Time'] = $table1Time[0]['table1Time'];
			// 督导教学巡(检)查情况记录表次数
			$connar1['where'] = $this->sqlEscape('admin_id=??', array($v['id']));
			$connar1['items'] = 'count(*) table2Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			}
			$table2Time = $this->Table2_model->get_list_full($connar1);

			$data['ret'][$k]['table2Time'] = $table2Time[0]['table2Time'];
			// 督导巡考情况记录表次数
			$connar1['where'] = $this->sqlEscape('admin_id=??', array($v['id']));
			$connar1['items'] = 'count(*) table3Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$table3Time = $this->Table3_model->get_list_full($connar1);
			$data['ret'][$k]['table3Time'] = $table3Time[0]['table3Time'];
			// 教学状态专项检查表次数
			$connar1['where'] = $this->sqlEscape('admin_id=??', array($v['id']));
			$connar1['items'] = 'count(*) table4Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$table4Time = $this->Table4_model->get_list_full($connar1);
			$data['ret'][$k]['table4Time'] = $table4Time[0]['table4Time'];
			// 学生顶岗实习巡查次数
			$connar1['where'] = $this->sqlEscape('admin_id=??', array($v['id']));
			$connar1['items'] = 'count(*) table5Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$table5Time = $this->Table5_model->get_list_full($connar1);
			$data['ret'][$k]['table5Time'] = $table5Time[0]['table5Time'];
			// 教学信息员专题调查表次数
			$connar1['where'] = $this->sqlEscape('admin_id=??', array($v['id']));
			$connar1['items'] = 'count(*) table6Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and sub_time>?? and sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$table6Time = $this->Table6_model->get_list_full($connar1);
			$data['ret'][$k]['table6Time'] = $table6Time[0]['table6Time'];

			$adminlist[$k]['admin_id'] = $v['id'];
			$adminlist[$v['id']]['count1'] = $table1Time[0]['table1Time'];
			$adminlist[$v['id']]['count2'] = $table2Time[0]['table2Time'];
			$adminlist[$v['id']]['count3'] = $table3Time[0]['table3Time'];
			$adminlist[$v['id']]['count4'] = $table4Time[0]['table4Time'];
			$adminlist[$v['id']]['count5'] = $table5Time[0]['table5Time'];
			$adminlist[$v['id']]['count6'] = $table6Time[0]['table6Time'];
		}

		// $this->otherStatistics($adminlist); //同步老师的反馈数据

		if (!$isExport) {
			echo json_encode($data);
		} else {
			// return $data["ret"];			
			$this->load->library('PHPExcel/PHPExcel');
			$excel = new PHPExcel();
			//公共样式
			$excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
			$excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

			$x_line = 0;
			$y_line = 1;

			//标题
			$title = array($_POST['start_time'] . '至' . $_POST['end_time'], '工号', '姓名', '反馈次数', '教学督导专题调研次数', '督导教学巡(检)查情况记录次数', '督导巡考情况记录次数', '教学状态专项检查次数', '学生顶岗实习巡查次数', '教学信息员专题调查次数');
			foreach ($title as $k => $v) {
				$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $v);
				$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line)->getFont()->setBold(true);
				$x_line++;
			}

			$x_line = 0;
			$y_line = 2;
			//正式数据

			foreach ($data['ret'] as $k => $v) {
				foreach ($v as $k1 => $v1) {
					if ($k1 == 'id') {
						$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $_POST['start_time'] . '至' . $_POST['end_time']);
					} else {
						$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $v1);
					}

					$x_line++;
				}
				$x_line = 0;
				$y_line++;
			}
			$write = new PHPExcel_Writer_Excel2007($excel);
			$director = "upload/export/" . date('Ymd', time()) . "/";
			if (!file_exists($director)) {
				//检查是否有该文件夹，如果没有就创建，并给予最高权限
				mkdir($director, 0777);
			}
			$file_name = '个人其他材料反馈报表' . date('Ymd') . '.xlsx';
			$write->save($director . iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址', true, $director . $file_name));
		}
	}



	//其他材料反馈报表
	function statisticsOtherDept($params = array(), $isExport = false)
	{
		$connar = array();
		if (!$isExport) {
			$params = $_POST;
			$connar = $this->Admin_model->pickPages($connar, $params);
		}
		// $connar['items']="id,dept_id,dept_name";
		// $connar['where']=$this->sqlEscape('status<>?? and  dept_id is not null',array(CommonStatus::Deleted[XPHEnum::Value]));
		// $connar['groupby']='dept_id';
		$where = 'where db_admin.status<>' . CommonStatus::Deleted[XPHEnum::Value] . ' and  db_admin.dept_id is not null and db_admin.dept_id<>0 AND (
							db_table1.admin_id IS NOT NULL
							OR db_table2.admin_id IS NOT NULL
							OR db_table3.admin_id IS NOT NULL
							OR db_table4.admin_id IS NOT NULL
							OR db_table5.admin_id IS NOT NULL
							OR db_table6.admin_id IS NOT NULL
						) and db_admin.dept_id<>0';
		$role = explode(',', getSess()['sub_role_id']);
		array_push($role, getSess()['role_id']);

		if (in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)) {
			$where .= ' and 1=1';
		} else {
			$where .= $this->sqlEscape(' and db_admin.dept_name=?? ', array(getSess()['dept_name']));
		}
		if (isset($params['start_time']) && isset($params['end_time'])) {
			// $where.=' and created>'.strtotime($params['start_time']).' and created<'.strtotime($params['end_time']);
			// $where.=$this->sqlEscape(" and db_table1.created>=?? and db_table1.created<??  and db_table2.created>=?? and db_table2.created<?? and db_table3.created>=?? and db_table3.created<?? and db_table4.created>=?? and db_table4.created<?? and db_table5.created>=?? and db_table5.created<?? and db_table6.created>=?? and db_table6.created<??",array(strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59')));
		} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// $where.=' and created>'.strtotime($_POST['start_time']).' and created<'.strtotime($_POST['end_time']);
		}
		if (!$isExport) {
			if (isset($params['pageNo']) && isset($params['pageSize'])) {
				$pageNo = $_POST['pageNo'] - 1;
				$pageSize = $_POST['pageSize'];
			} else {
				$pageNo = '1';
				$pageSize = '20';
			}
		}
		if (!$isExport) {
			$data = $this->Admin_model->get_page_list($connar);

			// $data['ret']=$this -> Admin_model -> query('select * from(select id,dept_id,dept_name from db_admin '.$where.' limit '.$pageSize*$pageNo.','.$pageSize.') attend group by dept_id');
			// $data['ret']=$this -> Admin_model -> query('select * from(select id,dept_id,dept_name from db_admin '.$where.') attend  group by dept_id limit '.$pageSize*$pageNo.','.$pageSize);
			$data['ret'] = $this->Admin_model->query('select * from(select distinct(db_admin.id),dept_id,dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
						LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
						LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
						LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
						LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
						LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id ' . $where . ') attend  group by dept_id 
						order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
			// echo $this->db->last_query();
			$count = $this->Admin_model->query('select count(*) count from (select * from (select distinct(db_admin.id),db_admin.dept_id,db_admin.dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
						LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
						LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
						LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
						LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
						LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id ' . $where . ' ) attend group by dept_id
						order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
			$data['count'] = $count[0]['count'];
			$data['totalPage'] = ceil($data['count'] / $pageSize);
		} else {
			$data['ret'] = $this->Admin_model->query('select * from(select distinct(db_admin.id),dept_id,dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
					LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
					LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
					LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
					LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id
					LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id ' . $where . ') attend  group by dept_id 
					order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
			$count = $this->Admin_model->query('select count(*) count from (select * from (select distinct(db_admin.id),db_admin.dept_id,db_admin.dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
					LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
					LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
					LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
					LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
					LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id ' . $where . ' ) attend group by dept_id
					order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
		}
		foreach ($data['ret'] as $k => $v) {
			// 反馈次数身份条件筛选
			if (in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)) {
				$where_role = ' and 1=1';
			} else if (in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)) {
				$where_role = $this->sqlEscape(' and db_admin.dept_name=?? ', array(getSess()['dept_name']));
			} else {
				$where_role = $this->sqlEscape(' and creator=?? ', array(getSess()['id']));
			}

			// 反馈次数
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where = ' and feedback.created>' . strtotime($params['start_time'] . '00:00:00') . ' and feedback.created<' . strtotime($params['end_time'] . '23:59:59');
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$where = $this->sqlEscape(' and feedback.created>?? and feedback.created<??', array(strtotime($_POST['start_time'] . "00:00:00"), strtotime($_POST['end_time'] . "23:59:59")));
			} else {
				$where = '';
			}
			$feedbackTime = $this->Student_feedback_model->query("select count(*) feedbackTime from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=" . CommonStatus::UnDeleted[XPHEnum::Value] . "  and (feedback.status=" . FeedBackStatus::HaveWrite[XPHEnum::Value] . " or feedback.status=" . FeedBackStatus::End[XPHEnum::Value] . ") and db_admin.dept_id=" . $v['dept_id'] . $where . $where_role);
			$data['ret'][$k]['feedbackTime'] = $feedbackTime[0]['feedbackTime'];


			// 其他材料次数身份条件筛选
			if (in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)) {
				$where_role = ' and 1=1';
			} else if (in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)) {
				$where_role = $this->sqlEscape(' and db_admin.dept_name=?? ', array(getSess()['dept_name']));
			} else {
				$where_role = $this->sqlEscape(' and admin_id=?? ', array(getSess()['id']));
			}

			// 教学督导专题教研次数
			$connar1['where'] = $this->sqlEscape('dept_id=??', array($v['dept_id']));
			$connar1['join'] = array('db_admin', 'db_admin.id=db_table1.admin_id');
			$connar1['items'] = 'count(*) table1Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table1.sub_time>?? and db_table1.sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table1.sub_time>?? and db_table1.sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$connar1['where'] .= $where_role;
			$table1Time = $this->Table1_model->get_list_full($connar1);
			$data['ret'][$k]['table1Time'] = $table1Time[0]['table1Time'];
			// 督导教学巡(检)查情况记录表次数
			$connar1['where'] = $this->sqlEscape('dept_id=??', array($v['dept_id']));
			$connar1['join'] = array('db_admin', 'db_admin.id=db_table2.admin_id');
			$connar1['items'] = 'count(*) table2Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table2.sub_time>?? and db_table2.sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table2.sub_time>?? and db_table2.sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$connar1['where'] .= $where_role;
			$table2Time = $this->Table2_model->get_list_full($connar1);
			// echo $this->db->last_query();
			// echo "<br/>";
			$data['ret'][$k]['table2Time'] = $table2Time[0]['table2Time'];
			// 督导巡考情况记录表次数
			$connar1['where'] = $this->sqlEscape('dept_id=??', array($v['dept_id']));
			$connar1['join'] = array('db_admin', 'db_admin.id=db_table3.admin_id');
			$connar1['items'] = 'count(*) table3Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table3.sub_time>?? and db_table3.sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table3.sub_time>?? and db_table3.sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$connar1['where'] .= $where_role;
			$table3Time = $this->Table3_model->get_list_full($connar1);
			$data['ret'][$k]['table3Time'] = $table3Time[0]['table3Time'];
			// 教学状态专项检查表次数
			$connar1['where'] = $this->sqlEscape('dept_id=??', array($v['dept_id']));
			$connar1['join'] = array('db_admin', 'db_admin.id=db_table4.admin_id');
			$connar1['items'] = 'count(*) table4Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table4.sub_time>?? and db_table4.sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table4.sub_time>?? and db_table4.sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$connar1['where'] .= $where_role;
			$table4Time = $this->Table4_model->get_list_full($connar1);
			$data['ret'][$k]['table4Time'] = $table4Time[0]['table4Time'];
			// 学生顶岗实习巡查次数
			$connar1['where'] = $this->sqlEscape('dept_id=??', array($v['dept_id']));
			$connar1['join'] = array('db_admin', 'db_admin.id=db_table5.admin_id');
			$connar1['items'] = 'count(*) table5Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table5.sub_time>?? and db_table5.sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and db_table5.sub_time>?? and db_table5.sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$connar1['where'] .= $where_role;
			$connar1['where'] .= $this->sqlEscape(' and db_table5.status=??', array(CommonStatus::UnDeleted[XPHEnum::Value]));
			$table5Time = $this->Table5_model->get_list_full($connar1);
			// echo $this->db->last_query();
			// echo "<br/>";
			$data['ret'][$k]['table5Time'] = $table5Time[0]['table5Time'];
			// 教学信息员专题调查表次数
			$connar1['where'] = $this->sqlEscape('dept_id=??', array($v['dept_id']));
			$connar1['join'] = array('db_admin', 'db_admin.id=db_table6.admin_id');
			$connar1['items'] = 'count(*) table6Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and table6.sub_time>?? and table6.sub_time<??', array(strtotime($params['start_time']), strtotime($params['end_time'])));
			} else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'] .= $this->sqlEscape(' and table6.sub_time>?? and table6.sub_time<??', array(strtotime($_POST['start_time']), strtotime($_POST['end_time'])));
			}
			$connar1['where'] .= $where_role;
			$table6Time = $this->Table6_model->get_list_full($connar1);
			$data['ret'][$k]['table6Time'] = $table6Time[0]['table6Time'];
		}
		// 去除都为0的记录
		// 		$ret=$data['ret'];
		// 		$data['ret']=array();
		// 		foreach ($ret as $k => $v) {
		// 			// $break=false;
		// 			// var_dump($v);
		// 			if(!($v['feedbackTime']=='0' && $v['table1Time']=='0'  && $v['table2Time']=='0' && $v['table3Time']=='0' && $v['table4Time']=='0' && $v['table5Time']=='0' && $v['table6Time']=='0')){

		// // var
		// 				// pri($data['ret'][$k]);
		// 				// unset($data['ret'][$k]);
		// 				// pri($data['ret']);
		// 				array_push($data['ret'], $v);

		// 			}else{
		// 				// var_dump('zl');
		// 				// exit;
		// 				$data['count']=$data['count']-1;
		// 			}
		// 			$data["totalPage"] = ceil($data["count"] / $params['pageSize']);

		// 		}
		// 		// 重置数组下标
		// 		$data['ret']=array_values($data['ret']);
		if (!$isExport) {

			$dept_count = 0;
			$feedbackTime_count = 0;
			$table1_count = 0;
			$table2_count = 0;
			$table3_count = 0;
			$table4_count = 0;
			$table5_count = 0;
			$table6_count = 0;
			// var_dump($data['ret'][0]['feedbackTime']);
			foreach ($data['ret'] as $k => $v) {
				$data['ret'][$k]['is_total'] = 0;
				$feedbackTime_count = $feedbackTime_count + $v['feedbackTime'];
				$table1_count += $v['table1Time'];
				$table2_count += $v['table2Time'];
				$table3_count += $v['table3Time'];
				$table4_count += $v['table4Time'];
				$table5_count += $v['table5Time'];
				$table6_count += $v['table6Time'];
			}
			$count = count($data['ret']);
			$data['ret'][$count]['dept_id'] = '-1';
			$data['ret'][$count]['dept_name'] = '合计';
			$data['ret'][$count]['feedbackTime'] = $feedbackTime_count;
			$data['ret'][$count]['table1Time'] = $table1_count;
			$data['ret'][$count]['table2Time'] = $table2_count;
			$data['ret'][$count]['table3Time'] = $table3_count;
			$data['ret'][$count]['table4Time'] = $table4_count;
			$data['ret'][$count]['table5Time'] = $table5_count;
			$data['ret'][$count]['table6Time'] = $table6_count;
			$data['ret'][$count]['is_total'] = 0;

			echo json_encode($data);
		} else {
			// return $data["ret"];
			$dept_count = 0;
			$feedbackTime_count = 0;
			$table1_count = 0;
			$table2_count = 0;
			$table3_count = 0;
			$table4_count = 0;
			$table5_count = 0;
			$table6_count = 0;
			// var_dump($data['ret'][0]['feedbackTime']);
			foreach ($data['ret'] as $k => $v) {
				$feedbackTime_count = $feedbackTime_count + $v['feedbackTime'];
				$table1_count += $v['table1Time'];
				$table2_count += $v['table2Time'];
				$table3_count += $v['table3Time'];
				$table4_count += $v['table4Time'];
				$table5_count += $v['table5Time'];
				$table6_count += $v['table6Time'];
			}
			array_push($data['ret'], array('title' => '合计', 'dept_count' => $count[0]['count'], 'feedback_count' => $feedbackTime_count, 'table1_count' => $table1_count, 'table2_count' => $table2_count, 'table3_count' => $table3_count, 'table4_count' => $table4_count, 'table5_count' => $table5_count, 'table6_count' => $table6_count));
			$this->load->library('PHPExcel/PHPExcel');
			$excel = new PHPExcel();
			//公共样式
			$excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
			$excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

			$x_line = 0;
			$y_line = 1;

			//标题
			$title = array($_POST['start_time'] . '至' . $_POST['end_time'], '学院', '反馈次数', '教学督导专题调研次数', '督导教学巡(检)查情况记录次数', '督导巡考情况记录次数', '教学状态专项检查次数', '学生顶岗实习巡查次数', '教学信息员专题调查次数');
			foreach ($title as $k => $v) {
				$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $v);
				$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line)->getFont()->setBold(true);
				$x_line++;
			}

			$x_line = 0;
			$y_line = 2;
			//正式数据

			foreach ($data['ret'] as $k => $v) {
				unset($v['dept_id']);
				foreach ($v as $k1 => $v1) {
					if ($k1 == 'id') {
						$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $_POST['start_time'] . '至' . $_POST['end_time']);
					} else {
						$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line) . $y_line, $v1);
					}

					$x_line++;
				}
				$x_line = 0;
				$y_line++;
			}
			$write = new PHPExcel_Writer_Excel2007($excel);
			$director = "upload/export/" . date('Ymd', time()) . "/";
			if (!file_exists($director)) {
				//检查是否有该文件夹，如果没有就创建，并给予最高权限
				mkdir($director, 0777);
			}
			$file_name = '其他材料反馈报表' . date('Ymd') . '.xlsx';
			$write->save($director . iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址', true, $director . $file_name));
		}
	}


	//学生信息反馈次数统计
	function feedbackStudentOther($params = array(), $isExport = false){
		$connar = array();
		$params = $_POST;
		if (!$isExport) {
			$connar = $this->Admin_model->pickPages($connar, $params);
		}
		$connar['items'] = "id,usercode student_code,username student_name,created,dept_id,dept_name"; 
		$connar['where'] = $this->sqlEscape('db_admin.status<>?? and (role_id=?? or role_id=?? or FIND_IN_SET(??,sub_role_id) or FIND_IN_SET(??,sub_role_id)) ', array(CommonStatus::Deleted[XPHEnum::Value],WebRole::StudentInfoOfficer[XPHEnum::Value],WebRole::UnitStudentInfoOfficer[XPHEnum::Value],WebRole::StudentInfoOfficer[XPHEnum::Value],WebRole::UnitStudentInfoOfficer[XPHEnum::Value]));
		$role = explode(',', getSess()['sub_role_id']);
		array_push($role, getSess()['role_id']);
		if (in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)) {
			$connar['where'] .= ' and 1=1 ';
		} else if (in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)) {
			$connar['where'] .= $this->sqlEscape(' and db_admin.dept_name=?? ', array(getSess()['dept_name']));
		} else if (in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::ExternalSupervisor[XPHEnum::Value], $role) || in_array(WebRole::SupervisorDirector[XPHEnum::Value], $role)) {
			$connar['where'] .= $this->sqlEscape(' and db_admin.id=?? ', array(getSess()['id']));
		}
		if (isset($params['dept_name'])) {
			$connar['where'] .= $this->sqlLikeEscape(' and dept_name like "%??%"', array($params['dept_name']));
		} else if (isset($_POST['dept_name'])) {
			$connar['where'] .= $this->sqlLikeEscape(' and dept_name like "%??%"', array($_POST['dept_name']));
		}
		if (isset($params['username'])) {
			$connar['where'] .= $this->sqlLikeEscape(' and username like "%??%"', array($params['username']));
		} else if (isset($_POST['username'])) {
			$connar['where'] .= $this->sqlLikeEscape(' and username like "%??%"', array($_POST['username']));
		}

		if (!$isExport) {
			$data = $this->Admin_model->get_page_list($connar);
			// echo $this->db->last_query();
		} else {
			$data["ret"] = $this->Admin_model->get_list_full($connar);
		}

		$data["ret"] = $this->get_array($data["ret"],$params);

		if (!$isExport) {
			echo json_encode($data);
		} else {
			//获取标题
			$month = $this->showMonthRange($params['start_time'],$params['end_time']);
			foreach($month as $k=>$v){
				$month[$k] = $v."月的反馈次数";
			}
			$month[count($month)] = "总计反馈次数";
			//标题
			$title = array('序号', '学号', '姓名');
			$new_title = array_merge($title,$month);

			//正式数据
			$new_arr = array();
			foreach($data['ret'] as $k=>$v){
				$new_arr[$k]['xuhao'] =$k+1;
				$new_arr[$k]['student_code'] = $v['student_code'];
				$new_arr[$k]['student_name'] = $v['student_name'];
				foreach($v['countNum'] as $k1=>$v1){
					$new_arr[$k]['countNum'.$k1] = $v1;
				}
			}

			//导出excel名称
			$name = '学生信息反馈次数报表'.$_POST['start_time'] . '至' . $_POST['end_time'];

			//导出表头名称
			$tableth = implode(",",$new_title);

			//new_list导出数据列,title 导出表名称,tableth表头名称		

		   $desc =  $this->export_service->export_excel($new_arr,$name,$tableth,2,3,$name);

		   //返回地址信息
		   $director = $desc['director'];$file_name = $desc['file_name'];     
			exit(retJson('返回地址',true,$director.$file_name));
		}
	}

	//根据日期获取当前月的第一天和最后一天
	function getthemonth($date){
		$firstday = date('Y-m-01', strtotime($date));
		$lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
		return array($firstday,$lastday);
	}

	//根据时间范围处理学生反馈信息每月反馈次数
	function get_array($array,$params){
		$admin_id_str =implode(",",array_column($array,"id")) ;
		$feedbacklist= $this->Student_feedback_model->query("select feedback.id,feedback.created,feedback.creator,db_admin.id admin_id,db_admin.usercode,db_admin.username from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=" . CommonStatus::UnDeleted[XPHEnum::Value] . " and (feedback.status=" . FeedBackStatus::HaveWrite[XPHEnum::Value] . " or feedback.status=" . FeedBackStatus::End[XPHEnum::Value] . ")  and db_admin.id in (" .$admin_id_str.") order by field(db_admin.id,".$admin_id_str.")");
		$new_feck_list = array();
		foreach($feedbacklist as $k=>$v){
			$new_feck_list[$v['usercode']][] = $v;//根据学生工号,重定义数组
		}

		$new_Arr = array();
		foreach ($array as $key => $val) {
			$month = $this->showMonthRange($params['start_time'],$params['end_time']);
			$sumNum = 0;
			foreach($month as $k=>$v){
				$day = $this->getthemonth($v);
				if($k == 0){
					$start_time	= $params['start_time'].' 00:00:00';
					$end_time =$day[1].' 23:59:59';
				}else if($k==count($month)-1){
					$start_time	= $day[0].' 00:00:00';
					$end_time =$params['end_time'].' 23:59:59';;
				}else{
					$start_time	= $day[0].' 00:00:00';
					$end_time =$day[1].' 23:59:59';
				}
				$new_Arr[$key]['id'] = $val['id'];
				$new_Arr[$key]['student_code'] = $val['student_code'];
				$new_Arr[$key]['student_name'] = $val['student_name'];
				if(!isset($new_feck_list[$val['student_code']])){	
					$new_Arr[$key]['countNum'][$k] = 0;	
				}else{
					$countNum = 0;
					foreach($new_feck_list[$val['student_code']] as $k1=>$v1){
						if($v1['created'] > strtotime($start_time) && $v1['created'] <= strtotime($end_time)){
							$countNum = $countNum+1;
						}	
					}
					$new_Arr[$key]['countNum'][$k] = $countNum;	
					$sumNum += $countNum;

				}
			}
			$new_Arr[$key]['month'] = count($month);
			$new_Arr[$key]['countNum'][count($month)] = $sumNum;
		}

		return $new_Arr;
	}


	//根据时间范围获取该时间范围的月份
	function get_student_month(){
		$start_time = $_POST['start_time'];
		$end_time = $_POST['end_time'];
		$month = $this->showMonthRange($start_time,$end_time);
		foreach($month as $k=>$v){
			$month[$k] = $v."月的反馈次数";
		}
		$month[count($month)] = "总计反馈次数";

		echo json_encode($month);
	}

	//根据时间范围获取该范围的月份
	function showMonthRange($start, $end) {
         $end = date('Y-m', strtotime($end)); // 转换为月
         $range = [];
         $i = 0;
         do {
             $month = date('Y-m', strtotime($start . ' + ' . $i . ' month'));
             $range[] = $month;
             $i++;
         } while ($month < $end);
 
         return $range;
    }


	
	

	//自动同步历史数据
	private function otherStatistics($admin)
	{
		$this->load->model('Other_statistics_model');
		//pri($admin);
		$admin_list = array_column($admin, 'admin_id');
		$admin_id = implode(",", $admin_list);

		// pri($admin_id);
		$connar1['where'] = " admin_id in (" . $admin_id . ")";
		$other = $this->Other_statistics_model->get_list_full($connar1);

		if (empty($other)) {
			foreach ($admin_list as $k => $v) {
				$adminarr = $this->Admin_model->get_single(array('id' => $v));
				$add = array(
					'admin_id'  => $v,
					'username'  => $adminarr['username'],
					'dept_name' => $adminarr['dept_name'],
					'specialResearch'      => $admin[$v]['count1'],
					'patrol'      => $admin[$v]['count2'],
					'tourInformant'      => $admin[$v]['count3'],
					'specialInspection'      => $admin[$v]['count4'],
					'postPractice'      => $admin[$v]['count5'],
					'messenger'      => $admin[$v]['count6'],
				);
				$this->Other_statistics_model->add($add);
			}
		} else {
			foreach ($other as $k => $v) {
				$update = array(
					'id' => $v['id'],
					'specialResearch'      => $admin[$v['admin_id']]['count1'],
					'patrol'      => $admin[$v['admin_id']]['count2'],
					'tourInformant'      => $admin[$v['admin_id']]['count3'],
					'specialInspection'      => $admin[$v['admin_id']]['count4'],
					'postPractice'      => $admin[$v['admin_id']]['count5'],
					'messenger'      => $admin[$v['admin_id']]['count6'],
				);
				$this->Other_statistics_model->update($update);
			}
		}
	}
}
